import java.sql.*;
import java.text.DateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;

public class SqliteHelper {
	private static Connection c = null;

	/**
	 * Ensures we always use the same connection string.
	 * 
	 * @return The connection string to the db.
	 * @throws Exception
	 */
	public static Connection getConn() throws Exception {
		if (c == null) {
			Class.forName("org.sqlite.JDBC");
			c = DriverManager.getConnection("jdbc:sqlite:tealServTest.db");
		}
		return c;
	}

	/**
	 * Returns a price for an item.
	 * 
	 * @param itemName
	 * @return The price for an item.
	 */
	public static double GetPriceByItemName(String itemName) {
		double itemPrice = 0.0;
		try {
			Statement selectStatement = c.createStatement();
			ResultSet selectResults = null;
			String selectQuery = "SELECT Item_Price FROM Menu WHERE Item_Name = '"
					+ itemName + "';";
			selectResults = selectStatement.executeQuery(selectQuery);
			while (selectResults.next()) {
				itemPrice = selectResults.getDouble("Item_Price");
			}

		} catch (Exception ex) {
			System.err.println("Database Call Failure in GetPriceByItemName.");
			System.err
					.println(ex.getClass().getName() + ": " + ex.getMessage());
			return 0;
		}
		return itemPrice;
	}

	/**
	 * Returns menu items by their specific category.
	 * 
	 * @param categoryName
	 *            The name of the food item's specific category.
	 * @return The ResultSet of the SELECT query.
	 */
	public static ArrayList<String> GetMenuItemsByCategory(String categoryName) {
		ResultSet itemResults = null;
		Connection con = null;
		ArrayList<String> itemList = new ArrayList();
		try {
			con = SqliteHelper.getConn();
			Statement menuSelectStatement = con.createStatement();
			String menuSelectQuery = "SELECT * FROM Menu WHERE Specific_Cat = '"
					+ categoryName + "';";
			itemResults = menuSelectStatement.executeQuery(menuSelectQuery);
			while (itemResults.next()) {
				itemList.add(itemResults.getString("Item_Name"));
			}

		} catch (Exception ex) {
			System.err
					.println("Database Call Failure in GetMenuItemsByCategory.");
			System.err
					.println(ex.getClass().getName() + ": " + ex.getMessage());
		}
		return itemList;
	}

	/**
	 * Inserts an Order into the DB
	 * 
	 * @param seatNumber
	 *            The seat number of the order.
	 * @param tableNumber
	 *            The table number of the order.
	 * @param itemName
	 *            The name of the item.
	 * @param itemPrice
	 *            The price of the item.
	 */
	public static void InsertOrder(int seatNumber, int tableNumber,
			String itemName, double itemPrice) {
		Connection con = null;
		try {
			con = SqliteHelper.getConn();
			Statement orderInsertStatement = con.createStatement();
			String orderInsertQuery = "INSERT INTO Orders VALUES ("
					+ seatNumber + ", " + tableNumber + ", '" + itemName
					+ "', " + itemPrice + ");";
			orderInsertStatement.executeUpdate(orderInsertQuery);
		} catch (Exception ex) {
			System.err.println("Database Call Failure in InsertOrder.");
			System.err
					.println(ex.getClass().getName() + ": " + ex.getMessage());
		}

	}

	/**
	 * Deletes an Order from the DB
	 * 
	 * @param seatNumber
	 *            The number of the seat to delete
	 * @param tableNumber
	 *            The table number of the order to delete.
	 */
	public static void DeleteOrder(int seatNumber, int tableNumber) {
		Connection con = null;
		try {
			con = SqliteHelper.getConn();
			Statement orderDeleteStatement = con.createStatement();
			String orderDeleteQuery = "DELETE FROM Orders WHERE Seat_Number = "
					+ seatNumber + "AND Table_Number = " + tableNumber + ";";
			orderDeleteStatement.executeUpdate(orderDeleteQuery);
		} catch (Exception ex) {
			System.err.println("Database Call Failure in DeleteOrder.");
			System.err
					.println(ex.getClass().getName() + ": " + ex.getMessage());
		}
	}

	/**
	 * Inserts a Reservation into the reservation table.
	 * 
	 * @param partySize
	 * @param name
	 * @param phoneNumber
	 * @param notes
	 */
	public static void InsertReservation(String theDate, String theTime,
			int partySize, String name, String phoneNumber, String notes) {
		Connection con = null;
		String incomingDateYear = theDate.substring(6, theDate.length());
		String incomingDateMonth = theDate.substring(0, 2);
		String incomingDateDay = theDate.substring(3, 5);
		String formattedDate = incomingDateYear + "-" + incomingDateMonth + "-"
				+ incomingDateDay;
		LocalDateTime reservationTime = LocalDateTime.parse(formattedDate + "T"
				+ theTime);
		String reservationTimeString = reservationTime.toString();
		try {
			con = SqliteHelper.getConn();
			Statement reservationInsertStatement = con.createStatement();
			String reservationInsertQuery = "INSERT INTO Reservation VALUES ('"
					+ reservationTimeString + "', " + partySize + ", '" + name
					+ "', '" + phoneNumber + "', '" + notes + "');";
			reservationInsertStatement.executeUpdate(reservationInsertQuery);
		} catch (Exception ex) {
			System.err.println("Database Call Failure in InsertReservation.");
			System.err
					.println(ex.getClass().getName() + ": " + ex.getMessage());
		}
	}

	/**
	 * Gets all reservations on or past the current day.
	 * 
	 * @return an ArrayList of reservation information
	 */
	public static ArrayList<String> GetActiveReservations() {
		ArrayList<String> activeReservationList = new ArrayList<>();
		Connection con = null;

		LocalDateTime currentTime = LocalDateTime.now();
		String currentTimeString = currentTime.toString();
		ResultSet reservationResults;

		try {
			con = SqliteHelper.getConn();
			Statement reservationSelectStatement = con.createStatement();
			String reservationSelectQuery = "SELECT * FROM Reservation WHERE Reservation_Time >= '"
					+ currentTimeString + "';";
			reservationResults = reservationSelectStatement
					.executeQuery(reservationSelectQuery);
			while (reservationResults.next()) {
				String resTimeStr = reservationResults
						.getString("Reservation_Time");
				LocalDateTime reservDate = LocalDateTime.parse(resTimeStr);
				String resDateStr = reservDate.getMonth().toString() + "-"
						+ reservDate.getDayOfMonth() + "-"
						+ reservDate.getYear();
				activeReservationList.add(resDateStr);

				LocalTime resResultTime = reservDate.toLocalTime();
				activeReservationList.add(resResultTime.toString());
				activeReservationList.add(reservationResults
						.getString("Party_Size"));
				activeReservationList.add(reservationResults.getString("Name"));
				activeReservationList.add(reservationResults
						.getString("Phone_Number"));
				activeReservationList
						.add(reservationResults.getString("Notes"));

			}
		} catch (Exception ex) {
			System.err
					.println("Database Call Failure in GetActiveReservations.");
			System.err
					.println(ex.getClass().getName() + ": " + ex.getMessage());
		}

		return activeReservationList;
	}

	/**
	 * Edits a reservation
	 * 
	 * @param editList
	 *            The list of new information
	 * @param nameStr
	 *            The name to update to
	 * @param partySize
	 *            The size of the party to update.
	 */
	public static void editReservation(ArrayList<String> editList,
			String nameStr, String partySize) {
		try {
			Statement reservationUpdateStatement = c.createStatement();
			String dateStr = editList.get(0);

			String incomingDateYear = dateStr.substring(6, dateStr.length());
			String incomingDateMonth = dateStr.substring(0, 2);
			String incomingDateDay = dateStr.substring(3, 5);
			String formattedDate = incomingDateYear + "-" + incomingDateMonth
					+ "-" + incomingDateDay;

			String timeStr = editList.get(1);

			LocalDateTime reservationTime = LocalDateTime.parse(formattedDate
					+ "T" + timeStr);

			String newReservationTime = reservationTime.toString();
			String updateString = "UPDATE Reservation SET Reservation_Time = '"
					+ newReservationTime + "', Party_Size = " + editList.get(2)
					+ ", Name = '" + editList.get(3) + "', Phone_Number = '"
					+ editList.get(4) + "', Notes = '" + editList.get(5)
					+ "' WHERE Party_Size = " + partySize + " AND Name = '"
					+ nameStr + "';";
			reservationUpdateStatement.executeUpdate(updateString);

		} catch (Exception ex) {
			System.err.println("Database Call Failure in editReservation.");
			System.err
					.println(ex.getClass().getName() + ": " + ex.getMessage());
		}

	}

}
